﻿CREATE PROCEDURE [dbo].[proc_Product_SalesStats_GetList]
(
	@CompanyId VARCHAR(1000),
	@NOE_Flag VARCHAR(10),
	@TypeId VARCHAR(10),
	@CateOneId VARCHAR(10),
	@CateTwoId VARCHAR(10),
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),
	@DeptId int
)
As
BEGIN
	Declare @cSql VarChar(8000),@Where1 VarChar(500),@Where2 VarChar(500)
	--初始化条件字符串
	Set @Where2 =''
	
	--指定日期
	Set @Where1 = 'CreateDate>=@sRq	And CreateDate<=@eRq'

	If @CompanyId <> '0'	--指定公司
		Set @Where1 = @Where1 + ' And CompanyId In(' + @CompanyId + ')'
		
	If @DeptId <>0	--指定部门
	Begin
		Set @Where1=@Where1 +' And deptid in(Select id From Department Where companyid='+@CompanyId+' And Left(bmbh,Len(@dep))=@dep)'
	End

	If @NOE_Flag <> '100'	--指定成单类别(常规、非常规、非企)
		Set @Where1 = @Where1 + ' And NOE_Flag=' + @NOE_Flag

	If @TypeId <> '0'	--指定成单类型(新增、续费)
		Set @Where1 = @Where1 + ' And TypeId=' + @TypeId

	If @CateOneId <> '0'	--产品的1级分类
		Set @Where2 = 'Where CateOneId=' + @CateOneId 

	If @CateTwoId <> '0'	--产品的2级分类
		Set @Where2 += ' And CateTwoId=' + @CateTwoId

	Set @cSql='Declare @sRq Datetime,@eRq Datetime,@dep varchar(30)
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
	'
	If @DeptId <>0	--指定部门
		Set @cSql = @cSql + 'Select @dep=bmbh From Department Where companyid='+@CompanyId+' And id='+Convert(varchar,@DeptId)

	Set @cSql = @cSql + '	
	;WITH List1 As (
		Select ProductId,Sum(SalePrice) As SalePriceCount,
		Sum(SalePrice)-Sum(CostPrice) As MaoliCount
		From Project
		Where ' + @Where1 + '
		And ProductId In(
			Select Id From Product ' + @Where2 + '
			)
		Group By ProductId
	)
	Select ROW_NUMBER() OVER (Order By a.CateOneId,a.CateTwoId,a.Id) As Row,
		a.Id,a.Title,
		IsNull((Select Title From Product_Category where Id=a.CateOneId),'''') As CateOneName,
		IsNull((Select Title From Product_Category where Id=a.CateTwoId),'''') As CateTwoName,
		IsNull(List1.SalePriceCount,0) As SalePriceCount,
		IsNull(List1.MaoliCount,0) As MaoliCount,
		IsNull(CustomerCount,0) AS CustomerCount,
			(
			 (Select Count(Distinct OrderId) From Project Where '+@Where1+' And SalePrice!=0 And ProductId=a.Id And  IsHedge=0) 
			+
			 (Select Count(Distinct OrderId) From Project Where '+@Where1+' And SalePrice!=0 And ProductId=a.Id And  IsHedge=-2)
			-
		     (Select Count(Distinct OrderId) From Project  Where '+@Where1+' And SalePrice!=0 And ProductId=a.Id And IsHedge=-1)
		   
		   ) As OrderCount  --成单数=修改的成单项+新建的成单项-对冲的成单项   ----2016.10.19 lht

		--((Select Count(Distinct OrderId) From Project 
		--	Where '+@Where1+' And SalePrice!=0 And ProductId=a.Id And (IsHedge=0 or IsHedge=-2)) - 
		-- (Select Count(Distinct OrderId) From Project Where '+@Where1+' And SalePrice!=0 And ProductId=a.Id And IsHedge=-1)) As OrderCount  --原有的成单数统计，如果单数出现偏差，就注销掉上面的，用这个

		--0 As OrderCount
	From Product a
	Join List1 On a.Id=List1.ProductId 
	LEFT JOIN (
			SELECT ProductId,COUNT(1) AS CustomerCount
			FROM (
				SELECT ProductId,CusId,SUM(OrderCount) AS OrderSUM
				FROM (
					SELECT ProductId,CusId,Count(Distinct OrderId) AS OrderCount
					FROM Project
					WHERE ' + @Where1 + ' AND SalePrice!=0 AND IsHedge=0
					GROUP BY ProductId,CusId

					UNION ALL

					SELECT ProductId,CusId,Count(Distinct OrderId) AS OrderCount
					FROM Project
					WHERE ' + @Where1 + ' AND SalePrice!=0 AND IsHedge=-2
					GROUP BY ProductId,CusId

					UNION ALL 

					SELECT ProductId,CusId,-Count(Distinct OrderId) AS OrderCount
					FROM Project
					WHERE ' + @Where1 + ' AND SalePrice!=0 AND IsHedge=-1
					GROUP BY ProductId,CusId
					) AS T1
				GROUP BY ProductId,CusId
				HAVING SUM(OrderCount) > 0
				) AS T2
			GROUP BY ProductId) AS T3 ON A.ID = T3.ProductId
	 '+ @Where2 + '
	Order By Row'

	Exec(@cSql)
	print (@cSql)
	return @cSql
End